package cn.com.headfree.expert.dao.impl;

import cn.com.headfree.entity.TEducateInfo;
import cn.com.headfree.entity.TEmpBaseInfo;
import cn.com.headfree.entity.TPartRank;
import cn.com.headfree.entity.TSeniorCompetent;
import cn.com.headfree.expert.dao.IUserDataForExpertDao;
import cn.com.headfree.vo.*;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.util.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Repository("userDataForExpertDao")
public class UserDataForExpertDaoImpl extends BaseDaoImpl<TEmpBaseInfo> implements IUserDataForExpertDao {

	@Override
	public TEmpBaseInfo getBaseInfoByErpNo(String erpNo) {
		String sql = " select t.*," +
				" GET_DIC('Nation',nation) as nation " +
                " from t_emp_base_info t where t.erp_no= ?  and  t.is_valid='0' and t.del_sign = '0' ";
		return this.findUnique(TEmpBaseInfo.class, sql, new Object[] { erpNo });
	}

	@Override
	public List<YearEffectVo> getYearEffectByErpNo(String erpNo){
		Calendar date = Calendar.getInstance();
		int currentYear = Integer.valueOf(date.get(Calendar.YEAR));
		int endYear = currentYear - 1;
		int startYear = currentYear - 3;
		StringBuilder sqlBuilder = new StringBuilder("select t.id,t.rate_year as year,GET_DIC('PerformLevelRecentThreeYears',perfor_grade) as perfor_grade from t_year_effect_info t where t.is_valid='0' and t.del_sign = '0' ");
		List<String> arryList = new ArrayList<String>();
		if (StringUtils.isNotBlank(erpNo)) {
			sqlBuilder.append(" AND t.erp_no= ? ");
			arryList.add(erpNo);
		}
		sqlBuilder.append(" AND t.rate_year BETWEEN ? and ? ");
		arryList.add(startYear+"");
		arryList.add(endYear+"");
		sqlBuilder.append(" order by t.rate_year desc");
		return this.findAllBySQL(YearEffectVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<HonorDatasVo> getProTecInfoByErpNo(String erpNo) {
		String sql = " select t.id,t.acqu_time,t.subj_name as honor_name,t.audit_org as award_unit,t.cert_no " +
				"  from t_pro_tec_info t  " +
                " where t.is_valid='0'  and t.del_sign = '0' ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(erpNo);
		sqlBuilder.append(" order by t.acqu_time desc");
		return this.findAllBySQL(HonorDatasVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<HonorDatasVo> getSkillLevelInfoByErpNo(String erpNo,String seniorId) {
		String sql = "  select t.id,t.acqu_time,t.audit_org as award_unit,h.is_hide, " +
				" GET_DIC('SkillAppraisalWork',tech_type) as honor_name " +
				" from t_skill_level_info t  " +
				" LEFT JOIN t_senior_competent_handle h on t.id = h.business_id and h.senior_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(seniorId);
		arryList.add(erpNo);
		sqlBuilder.append(" order by t.acqu_time desc");
		return this.findAllBySQL(HonorDatasVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<ExpertTitleVo> getExpertTitleInfoByErpNo(String erpNo,String seniorId) {
		String sql = "  select t.id,t.acqu_time,h.is_hide, " +
				" GET_DIC('TalentTitle',t.emp_title) as honor_name " +
				" from t_expert_title_info t " +
				" LEFT JOIN t_senior_competent_handle h on t.id = h.business_id and h.senior_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(seniorId);
		arryList.add(erpNo);
		sqlBuilder.append(" order by t.acqu_time desc");
		return this.findAllBySQL(ExpertTitleVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<AcadeAchiveVo> getAcadeAchiveInfoByErpNo(String erpNo, Date acquTime) {
		String sql = " select t.id,t.pub_time,t.acad_name,t.pub_name,t.acad_type,t.publisher, " +
				" GET_DIC('XueshuchengguoJb',t.level) as pub_type, " +
				" GET_DIC('XueshuchengguoJs',r.role) as my_duty " +
				" from t_acade_achive_info t " +
				" left join t_part_rank r on t.id = r.item_id " +
				" where  r.item_type='12' and t.is_valid='0'and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<Object> arryList = new ArrayList<Object>();
		sqlBuilder.append(" AND r.erp_no= ? ");
		arryList.add(erpNo);
		arryList.add(erpNo);
		if(acquTime != null){
			sqlBuilder.append(" AND t.pub_time > ? ");
			arryList.add(acquTime);
		}
		sqlBuilder.append(" order by t.pub_time desc");
		return this.findAllBySQL(AcadeAchiveVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<StandInfoVo> getStandInfoByErpNo(String erpNo,Date acquTime) {
		String sql = "  select t.id,t.issue_time,t.name,  " +
				" GET_DIC('XueshuchengguoJs',r.role) as my_duty  " +
				" from t_stand_info t  " +
				" left join t_part_rank r on r.item_id=t.id " +
				" where  r.item_type='13' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<Object> arryList = new ArrayList<Object>();
		sqlBuilder.append(" AND r.erp_no= ? ");
		arryList.add(erpNo);
		arryList.add(erpNo);
		if(acquTime != null){
			sqlBuilder.append(" AND t.issue_time > ? ");
			arryList.add(acquTime);
		}
		sqlBuilder.append(" order by t.issue_time desc");
		return this.findAllBySQL(StandInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<SkillAndTechnicalVo> getPatentInfoByErpNo(String erpNo) {
		String sql = "   select t.id,t.patent_name as project_name,t.decl_date as start_time,  " +
				"  GET_DIC('XueshuchengguoJs',r.role) as my_duty, " +
				"  GET_DIC('PatentType',t.patent_type) as encourage " +
				"  from t_patent_info t  " +
				" left join t_part_rank r on r.item_id=t.id " +
				" where r.item_type='14'  and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND r.erp_no= ? ");
		arryList.add(erpNo);
        arryList.add(erpNo);
		sqlBuilder.append(" order by t.decl_date desc");
		return this.findAllBySQL(SkillAndTechnicalVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<SkillAndTechnicalVo> getTecInnovInfoByErpNo(String erpNo,String seniorId) {
		String sql = "  select t.id,t.award_date as start_time,t.achi_name as project_name,t.award_name as encourage,h.is_hide, " +
				" GET_DIC('XueshuchengguoJs',r.role) as my_duty " +
				" from t_tec_innov_info t  " +
				"  left join t_part_rank r on r.item_id = t.id " +
				" LEFT JOIN t_senior_competent_handle h on h.business_id = t.id and h.senior_id = ? and h.erp_no = ? " +
				" where  r.item_type='15' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND r.erp_no= ? ");
		arryList.add(seniorId);
		arryList.add(erpNo);
        arryList.add(erpNo);
		sqlBuilder.append(" order by t.award_date desc");
		return this.findAllBySQL(SkillAndTechnicalVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<TecManInnovVo> getTecManInnovByErpNo(String erpNo,Date acquTime) {
		String sql = "  select t.id,t.achi_name,t.award_date,t.award_org,r.rank, " +
				" GET_DIC('KjglAchiveType',t.achi_type) as achi_type, " +
				" GET_DIC('JygexinQcJb',t.award_level) as award_level, " +
				" GET_DIC('JygexinQcDj',t.award_grade) as award_grade " +
				" from t_tec_man_innov t  " +
				" left join t_part_rank r on r.item_id=t.id " +
				" where r.item_type='16' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<Object> arryList = new ArrayList<Object>();
		sqlBuilder.append(" AND r.erp_no= ? ");
		arryList.add(erpNo);
		arryList.add(erpNo);
		if(acquTime != null){
			sqlBuilder.append(" AND t.award_date > ? ");
			arryList.add(acquTime);
		}
		sqlBuilder.append(" order by award_date desc");
		return this.findAllBySQL(TecManInnovVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<HonorDatasVo> getHonnorInfoByErpNo(String erpNo,String seniorId,Date acquTime) {
		String sql = " select t.id,t.acqu_time,t.honor_title as honor_name,t.award_org as award_unit,h.is_hide " +
				" from t_honor_info t " +
				" LEFT JOIN t_senior_competent_handle h on t.id = h.business_id and h.senior_id = ? " +
                " where t.is_valid='0'  and t.del_sign = '0' ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<Object> arryList = new ArrayList<Object>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(seniorId);
		arryList.add(erpNo);
		if(acquTime != null){
			sqlBuilder.append(" AND t.acqu_time > ? ");
			arryList.add(acquTime);
		}
		sqlBuilder.append(" order by t.acqu_time desc");
		return this.findAllBySQL(HonorDatasVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<WorkExpInfoVo> getWorkExpInfoByErpNo(String erpNo,String seniorId) {
		String sql = "  select t.id,t.start_date as start_time,t.end_date as end_time,t.company,t.dept,t.post,t.job_content,h.is_hide, " +
				"  GET_DIC('DutyLevel',t.rank) as rank " +
				" from t_work_exp_info t " +
				" LEFT JOIN t_senior_competent_handle h on t.id = h.business_id and h.senior_id = ? " +
                " where  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(seniorId);
		arryList.add(erpNo);
		sqlBuilder.append(" order by t.start_date desc");
		return this.findAllBySQL(WorkExpInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<WorkExpInfoVo> getWorkStatusExpInfoByErpNo(String erpNo) {
		String sql = "  select t.id,t.start_date as start_time,t.end_date as end_time,t.company,t.dept,t.post,t.job_content, " +
				"  GET_DIC('DutyLevel',t.rank) as rank " +
				" from t_work_exp_info t " +
				" where  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(erpNo);
		sqlBuilder.append(" order by t.start_date desc");
		return this.findAllBySQL(WorkExpInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<EducateInfoVo> getEducateInfoByErpNo(String erpNo,String seniorId) {
		String sql = " select t.id,t.acqu_time,t.grad_school,t.major,t.is_employ_educ,h.is_hide, " +
				"  GET_DIC('PersonnelQualifications',t.educ_name) as educ_name, " +
				"  GET_DIC('AcademicDegree',t.degree_name) as degree_name " +
				" from t_educate_info t " +
				" LEFT JOIN t_senior_competent_handle h on t.id = h.business_id and h.senior_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' AND (t.is_employ_educ = '0' or t.is_employ_educ is null or ((t.is_employ_educ = '1') and t.educ_name<40))  ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(seniorId);
		arryList.add(erpNo);
		sqlBuilder.append(" order by t.acqu_time desc,t.grad_school ");
		return this.findAllBySQL(EducateInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

	@Override
	public List<StudyAndTrainVo> getContiStudyInfoByErpNo(String erpNo) {
		String sql = " select t.id,t.start_time,t.end_time,t.study_place as school_and_unit,t.major,t.certifier " +
				" from t_conti_study_info t " +
                " where t.is_valid='0'  and t.del_sign = '0' and t.type = '01' ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		List<String> arryList = new ArrayList<String>();
		sqlBuilder.append(" AND t.erp_no= ? ");
		arryList.add(erpNo);
		sqlBuilder.append(" order by t.start_time desc");
		return this.findAllBySQL(StudyAndTrainVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

    @Override
    public List<TeachArtVo> getContiStudyTechInfoByErpNo(String erpNo) {
        String sql = " select t.id,t.start_time,t.end_time,t.major as content,t.certifier " +
                " from t_conti_study_info t " +
                " where t.is_valid='0'  and t.del_sign = '0' and t.type = '02' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        arryList.add(erpNo);
        sqlBuilder.append(" order by t.end_time desc");
        return this.findAllBySQL(TeachArtVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

	@Override
	public  List<TPartRank> getTPartRankByMids(List<String> mainIds, String type){
		if(mainIds.isEmpty()||StringUtil.isNullOrEmpty(type)){
			return null;
		}
		String idsStr="";
		for(int i=0;i<mainIds.size();i++){
			idsStr+=",'"+mainIds.get(i)+"'";
		}
		idsStr=idsStr.substring(1);
		String jsCode="";
		if(type.equals("17")){
			jsCode="XiangMuKeTiJS";
		}else {
			jsCode="XueshuchengguoJs";
		}
		String sql = " select t.* ,GET_DIC('"+ jsCode +"',t.role) as role_name from t_part_rank t  " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" where item_id in (").append(idsStr).append(") and item_type='").append(type).append("' order by item_type, rank ");
		return this.findAllBySQL(TPartRank.class, sqlBuilder.toString(), null, false,null);

	}

    @Override
    public List<TeachArtVo> getTSdtTutorPrentInfoByErpNo(String erpNo,String seniorId) {
        String sql = " select t.id,t.start_date as start_time,t.end_date as end_time,t.teach_summary as content,h.is_hide " +
                " from t_sdt_tutor_prent_info t " +
                " LEFT JOIN t_senior_competent_handle h on t.id = h.business_id and h.senior_id = ? " +
                " where t.is_valid='0'  and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        arryList.add(seniorId);
        arryList.add(erpNo);
        sqlBuilder.append(" order by t.start_date desc");
        return this.findAllBySQL(TeachArtVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

	@Override
	public TSeniorCompetent getTSeniorCompetentById(String id) {
		String sql = " select t.id,t.erp_no,t.user_name,t.current_subj_level,t.acqu_time,t.year,t.audit_unit,t.cert_no,t.org_name, " +
				"  GET_DIC('professionalSenior',t.ready_subj_level) as ready_subj_level, " +
				"  GET_DIC('ProWorkTypes',t.branch_subj) as branch_subj " +
				" from t_senior_competent t " +
				" where  t.del_sign = '0' and t.id = ?  order by t.create_time desc";
		return this.findUnique(TSeniorCompetent.class, sql, new Object[] { id });
	}

	@Override
	public List<EducateInfoVo> getEducateInfoHighEducByErpNo(String erpNo) {
		String sql = " select t.id,t.major,t.grad_school,t.educ_name,t.degree_name " +
				" from t_educate_info t " +
				" where t.is_valid='0' and t.del_sign = '0' and t.is_highest_educ = '0' ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" AND t.erp_no= ? ");
		sqlBuilder.append(" order by t.acqu_time desc");
		List<String> arryList = new ArrayList<String>();
		arryList.add(erpNo);
		return this.findAllBySQL(EducateInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

}
